<?php
/**
 *
 * @author Amissah Gideon<kuulmek@yahoo.com>
 * @access public
 * @version 1.0
 * @copyright (c) 2011.
 */

class ReportController extends AppController {
    # Controller Name to be used
   var $name = 'Report';

  # Models to be used
   var $uses = array(
       'User',
       'Organisation',
       'Module',
       'Configuration',
       'PropertyItem',
       'PropertyItemDetail',
       'PropertyGroup',
       'Location',
       'PropertyGroupType',
       'PropertyGroupSubType',
       'Bill',
       'BillComponent',
       'BillProperty',
       'BillPayment'  
   );
    # Helpers and components


   # set the layout to use
   var $layout = 'report_layout';
   
   # set the name of this module
   var $moduleName = 'Report';

   # Check the authenticity of the user
   function  beforeFilter() {
          parent::setUpGlobalConfig();  
          # Check the user validity
          if($this->Session->check('userSessionData')){
                 $loggedUser =  $this->Session->read('userSessionData');
                 $userData = $this->User->getUserById($loggedUser['id']);
                 $userModules = $userData['Module'];
                 $allowed = false;
                 foreach($userModules as $module){
                    if($module['name'] === $this->moduleName){
                        $allowed = true;
                    }
                 }
                 if(!$allowed){
                      $this->redirect(array('controller' => 'Dashboard', 'action' => 'index'));
                 }
          }
          else{
                  $this->redirect('/');
          }
   }
   
   # Controller actions
   /**
    * This function displays all the users in the system
    * @name index
    * @param void
    * @return Array of data.
    * @access public
    */
   function index(){			
	   $prpGroup = Hash::combine($this->PropertyGroup->find('all',array('conditions' => array('deleted'=>0),'fields'=>array('id','group_name'),'recursive'=>-1)),'{n}.PropertyGroup.id', '{n}.PropertyGroup');
        $jsonArr = array();
        foreach($prpGroup as $key => $value){
            $jsonArr[] = array('name'=>$value['group_name'],'value'=>$value['id']);
        }

        $prpFieldGroup = $this->PropertyGroup->find('all',array(
                'fields'=>array('id','group_name','fields'),
                'conditions' => array('deleted'=>0),
                'contain'=>array(
                    'PropertyGroupType'=>array(
                        'fields'=>array('id','type_name'),
                        'PropertyGroupSubType'=>array('fields'=>array('id','sub_type_name'))
                    ),
                ),
                'recursive'=>2
            )
        );
        //debug($prpFieldGroup);
        $prpGroupData = array();
        foreach($prpFieldGroup as $value){
            $prpGroupData[$value['PropertyGroup']['id']]['id'] = $value['PropertyGroup']['id'];
            $prpGroupData[$value['PropertyGroup']['id']]['group_name'] = $value['PropertyGroup']['group_name'];
            
            if($value['PropertyGroup']['fields']){
                $fields_arr = explode(',',$value['PropertyGroup']['fields']);
                foreach($fields_arr as $value_0){
                    $prpGroupData[$value['PropertyGroup']['id']]['fields'][] = $value_0;
                }
            }
            else{
                $prpGroupData[$value['PropertyGroup']['id']]['fields'] = array();
            }
            
            foreach($value['PropertyGroupType'] as $value_1){
                $prpGroupData[$value['PropertyGroup']['id']]['group_type'][$value_1['id']]['id'] = $value_1['id'];
                $prpGroupData[$value['PropertyGroup']['id']]['group_type'][$value_1['id']]['type_name'] = $value_1['type_name'];
                
                foreach($value_1['PropertyGroupSubType'] as $value_2){
                    $prpGroupData[$value['PropertyGroup']['id']]['group_type'][$value_1['id']]['group_sub_type'][$value_2['id']]['id'] = $value_2['id'];
                    $prpGroupData[$value['PropertyGroup']['id']]['group_type'][$value_1['id']]['group_sub_type'][$value_2['id']]['sub_type_name'] = $value_2['sub_type_name'];
                }
            }
            
        }
        
        $loc_data = Hash::combine($this->Location->find('all',array(
                'fields'=>array('id','location_name','location_code'),
                'conditions' => array('deleted'=>0),
                'recursive'=>-1
            )
        ),'{n}.Location.id', '{n}.Location');
        
        $bill_years = $this->Bill->find('list', array('fields' => array('year', 'year')));
        $bill_year_opt = array('All'=>'All') + $bill_years;
        $bill_year_opt = $bill_years;
        $this->set(compact('jsonArr','prpGroupData','loc_data','bill_year_opt')); 
   }
   
   
   function getData (){         
        $id = $_POST['id'];
        $location_name = $_POST['location_name'];
        $group_id = $_POST['group_id'];
        $group_name = $_POST['group_name'];
        $type = $_POST['type'];
        $acct_report_type = $_POST['acct_report_type'];
        $revenue_year = $_POST['revenue_year'];
        
        if($type == 'property'){
            $frmtGroup = $this->__getPropertyReport($id);
        }
        else{
            $frmtGroup = $this->__getAccoutnReport($id, $acct_report_type, $revenue_year, $group_id);
        }
        
        $this->set(compact('frmtGroup','location_name','type','acct_report_type','revenue_year','group_name'));
   }
   
   
   function __viewOrPrintData ($idsP, $group_idP){        
        $prpGroup = $this->PropertyGroup->find('first',array(
                'fields'=>array('id','group_name','fields'),
                'conditions' => array('id'=>$group_idP,'deleted'=>0),
                'recursive'=>-1
            )
        );
        $groupFileds = explode(',',$prpGroup['PropertyGroup']['fields']);
        
        $ids = explode(',',$idsP);
        array_pop($ids);
        $prpData = $this->PropertyItem->find('all',array(
                'fields'=>array('id','name','property_reg_no','loc_region','owners_name'),
                'conditions' => array('PropertyItem.id'=>$ids, 'PropertyItem.deleted'=>0 ),
                'contain'=>array(
                    'PropertyGroup'=>array(
                        'fields'=>array('id','group_name'),
                    ),
                    'PropertyItemDetail'=>array('fields'=>array('id','permit_no')),
                    'PropertyGroupType'=>array('fields'=>array('id','type_name')),
                    'PropertyGroupSubType'=>array('fields'=>array('id','property_group_type_id','sub_type_name'))
                ),
                'recursive'=>1
            )
        );
        
        $return_arr = array('properties'=>$prpData, 'fields'=>$groupFileds);
        
        return $return_arr;
   }   
   
   
   function viewData (){
        
        $return = $this->__viewOrPrintData($_POST['ids'], $_POST['group_id']);
        $prpData = $return['properties'];
        $groupFileds = $return['fields'];
         
        $this->set(compact('prpData','groupFileds'));  
   }
   
   
   function printData (){
        $this->layout = 'print_layout';
        
        $OrgObj =  ClassRegistry::init('Organisation');
        $org = $OrgObj->find('first');
        $org = $org['Organisation'];
        
        $print_title = $_POST['formTitle'];
       $print_title = strtoupper($print_title);
        $return = $this->__viewOrPrintData($_POST['formIds'], $_POST['formGroupid']);
        $prpData = $return['properties'];
        $groupFileds = $return['fields'];
         
        $this->set(compact('prpData','groupFileds','print_title','org'));  
   }
   
   
   function printRevenueDebtors (){
        $this->layout = 'print_layout';
        
        $OrgObj =  ClassRegistry::init('Organisation');
        $org = $OrgObj->find('first');
        $org = $org['Organisation'];
        
        $id = $_GET['id'];
        $type = $_GET['type'];
        $location_name = $_GET['location_name'];
        $year = $_GET['year'];
        $group_id= $_GET['group_id'];
        $group_name= $_GET['group_name'];
        
        if($type == 'debtors'){
            $frmtGroup = $this->__debtorsReport($id, $group_id);
            $print_title = "Debtors list for $group_name at $location_name";
        }
        else{
            $frmtGroup = $this->__revenueReport($id, $year, $group_id);
            $print_title = "Revenue from $group_name for year $year at $location_name";
        }

       $print_title = strtoupper($print_title);
        
        $this->set(compact('print_title','frmtGroup','type','location_name','year','org','group_name'));  
   }
   
   
    function __sumBillTotals($data){
        if(is_array($data)){
            $ttl = 0;
            foreach($data as $value){
               $ttl = $ttl + $value['amount'];
            }
            $returnVal = $ttl; 
        }
        else{
            $totals = $this->BillComponent->find('all', array(
                'fields' => array('SUM(BillComponent.amount) as total'),
                'conditions'=>array('BillComponent.bill_id'=>$data, 'BillComponent.deleted' => 0)
                )
            );
            
            //debug($totals);
            $returnVal = $totals[0][0]['total'];
        }
        return number_format($returnVal,2);
    }
    
    
    function __sumPaymentTotals($data){
        if(is_array($data)){
            $ttl = 0;
            foreach($data as $value){
               $ttl = $ttl + $value['amount'];
            }
            $returnVal = $ttl;
        }
       /// else{
           // $totals = $this->BillComponent->find('all', array(
               // 'fields' => array('SUM(BillComponent.amount) as total'),
               // 'conditions'=>array('BillComponent.bill_id'=>$data, 'BillComponent.deleted' => 0)
               // )
          //  );
            
            //debug($totals);
           // $returnVal = $totals[0][0]['total'];
       // }
        return number_format($returnVal,2);
    }
   
   
    function __getPropertyReport($id){
        $prpData = $this->PropertyItem->find('all',array(
                'fields'=>array('id','property_group_id','location_id','loc_region','property_group_type_id','property_group_sub_type_id'),
                'conditions' => array('PropertyItem.location_id'=>$id, 'PropertyItem.deleted'=>0 ),
                'contain'=>array(
                    'PropertyGroup'=>array(
                        'fields'=>array('id','group_name','fields'),
                    ),
                    'PropertyItemDetail'=>array('fields'=>array('id','permit_no')),
                    'PropertyGroupType'=>array('fields'=>array('id','type_name')),
                    'PropertyGroupSubType'=>array('fields'=>array('id','property_group_type_id','sub_type_name'))
                ),
                'recursive'=>1
            )
        );
        
        /** Fisrt Group by Property Group **/
        $frmtGroup = array();
        foreach($prpData as $key => $value){
           $id =  $value['PropertyGroup']['id'];
           $frmtGroup[$id]['name']  = $value['PropertyGroup']['group_name'];
           $frmtGroup[$id]['fields']  = explode(',',$value['PropertyGroup']['fields']);
           $frmtGroup[$id]['items'][$key]  = $value;
        }
        
        /** Process count, ids, and other fields **/
        foreach($frmtGroup as $key => $value){
           $count = count($value['items']);
           $frmtGroup[$key]['item_count']  = $count;
           /** Store the ids for total count**/
           $prp_id='';
           foreach($value['items'] as $value_x){ 
                $prp_id .= $value_x['PropertyItem']['id'].',';
           }
           $frmtGroup[$key]['prp_ids']  = $prp_id;
           
           /** group type **/
           if(in_array("property_group_type_id", $value['fields'])) {
                /** Put items in Group type**/
                $groups_arr = array();
                foreach($value['items'] as $key_1 => $value_1){ 
                    $group_type_id = $value_1['PropertyGroupType']['id'];
                    $group_type_name = $value_1['PropertyGroupType']['type_name'];
                    $groups_arr[$group_type_id]['id']= $group_type_id;
                    $groups_arr[$group_type_id]['name']= $group_type_name;
                    $groups_arr[$group_type_id]['items'][$key_1]= $value_1;
                }
                krsort($groups_arr);
                /** Count and process them**/
                $frmtGroup[$key]['group_types_count']  = count($groups_arr);
                foreach($groups_arr as $key_2 => $value_2){ 
                    $frmtGroup[$key]['group_types'][$key_2]['id'] = $key_2;
                    $frmtGroup[$key]['group_types'][$key_2]['name'] = $value_2['name'];
                    $frmtGroup[$key]['group_types'][$key_2]['count'] = count($value_2['items']);
                    $prp_id_1 = '';
                    foreach($value_2['items'] as $value_3){ 
                        $prp_id_1 .= $value_3['PropertyItem']['id'].',';
                    }
                    $frmtGroup[$key]['group_types'][$key_2]['prp_ids'] = $prp_id_1;
                }
           }
           
           /** group sub type **/
           if(in_array("property_group_sub_type_id", $value['fields'])) {
                /** Put items in Group sub type**/
                $groups_sub_arr = array();
                foreach($value['items'] as $key_1 => $value_1){ 
                    $group_type_sub_id = $value_1['PropertyGroupSubType']['id'];
                    $group_type_id = $value_1['PropertyGroupSubType']['property_group_type_id'];
                    $group_type_sub_name = $value_1['PropertyGroupSubType']['sub_type_name'];
                    
                    $groups_sub_arr[$group_type_sub_id]['id']= $group_type_id;
                    $groups_sub_arr[$group_type_sub_id]['group_type_id']= $group_type_id;
                    $groups_sub_arr[$group_type_sub_id]['name']= $group_type_sub_name;
                    $groups_sub_arr[$group_type_sub_id]['items'][$key_1]= $value_1;
                }
                krsort($groups_sub_arr);
                /** Push group sub type to group typ and count them**/
                foreach($groups_sub_arr as $key_2 => $value_2){
                    $g_type_id = $value_2['group_type_id'];
                    $frmtGroup[$key]['group_types'][$g_type_id]['sub_types'][$key_2]['id'] = $value_2['id'];
                    $frmtGroup[$key]['group_types'][$g_type_id]['sub_types'][$key_2]['name'] = $value_2['name'];
                   /** $frmtGroup[$key]['group_types'][$g_type_id]['sub_types'][$key_2]['items'] = $value_2['items']; */
                    $frmtGroup[$key]['group_types'][$g_type_id]['sub_types'][$key_2]['count'] = count($value_2['items']);
                    $prp_id_2 = '';
                    foreach($value_2['items'] as $value_3){ 
                        $prp_id_2 .= $value_3['PropertyItem']['id'].',';
                    }
                    $frmtGroup[$key]['group_types'][$g_type_id]['sub_types'][$key_2]['prp_ids'] = $prp_id_2;
                }
           }
           
           /** permit no **/
           if (in_array("permit_no", $value['fields'])) {
                $number_permit = 0;
                $prp_id_permit = '';
                $number_no_permit = 0;
                $prp_id_no_permit = '';
                foreach($value['items'] as $key_1 => $value_1){ 
                    $permit_no = $value_1['PropertyItemDetail']['permit_no'];
                    if(empty($permit_no)){
                        $number_no_permit++;
                        $prp_id_no_permit .= $value_1['PropertyItem']['id'].',';
                    }
                    else{
                        $number_permit++;
                        $prp_id_permit .= $value_1['PropertyItem']['id'].',';
                    }
                }
                $frmtGroup[$key]['number_permit']['count']  = $number_permit;
                $frmtGroup[$key]['number_permit']['prp_ids']  = $prp_id_permit;
                $frmtGroup[$key]['number_no_permit']['count']  = $number_no_permit;
                $frmtGroup[$key]['number_no_permit']['prp_ids'] = $prp_id_no_permit;
           }
           
           $frmtGroup[$key]['items'] = '';
        }
        
        return $frmtGroup;
   }
   
   
   function __getAccoutnReport($id, $type, $revenue_year, $group_id){
        if($type == 'debtors'){
            return $this->__debtorsReport($id, $group_id);
        }
        else{
            return $this->__revenueReport($id, $revenue_year, $group_id);
        }
   }
   
   
   function __debtorsReport($id, $group_id){
        $prpData = $this->PropertyItem->find('all',array(
                'fields'=>array('id','name','property_reg_no','owners_name','telephone'),
                'conditions' => array('PropertyItem.location_id'=>$id, 'PropertyItem.property_group_id'=>$group_id, 'PropertyItem.deleted'=>0),
                'recursive'=>-1
            )
        );
        
        $prp_ids_arr = array();
        $frmt_properties = array();
        foreach($prpData as $key => $value){
            $prp_id = $value['PropertyItem']['id'];
            $prp_ids_arr[] = $prp_id;
            $frmt_properties[$prp_id] = $value;
            $frmt_properties[$prp_id]['BillProperty'] = array();
        }
        
        $billPropData = $this->BillProperty->find('all',array(
                'fields'=>array('id','bill_id','property_item_id'),
                'conditions' => array('BillProperty.property_item_id'=>$prp_ids_arr),
                'contain'=>array(
                    'Bill'=>array(
                        'fields'=>array('id','year'),
                    ),
                    'BillPayment'=>array('fields'=>array('id','amount')),
                ),
                'recursive'=>1
            )
        );
        
        foreach($billPropData as $key => $value){
          $prop_item_id =  $value['BillProperty']['property_item_id'];
          $frmt_properties[$prop_item_id]['BillProperty'][$key] = $value;
        }
        
        
        //debug($frmt_properties);
        
        $formatPrp = array();
        foreach($frmt_properties as $key => $value){
            $formatPrp[$key]['Property']['name'] = $value['PropertyItem']['name'];
            $formatPrp[$key]['Property']['property_reg_no'] = $value['PropertyItem']['property_reg_no'];
            $formatPrp[$key]['Property']['owners_name'] =  $value['PropertyItem']['owners_name'];
            $formatPrp[$key]['Property']['telephone'] =  $value['PropertyItem']['telephone'];

            /** Process Bill **/
            if($value['BillProperty']){
                $ttl_bill = 0.00;
                $ttl_payment = 0.00;
                foreach($value['BillProperty'] as $key1 => $value1){
                    $bill_comp_ttl = $this->__sumBillTotals($value1['Bill']['id']);
                    if($value1['BillPayment']){
                        $bill_payment_ttl =  $this->__sumPaymentTotals($value1['BillPayment']);
                    }
                    else{
                        $bill_payment_ttl = 0.00;
                    }
                    /** Totaling **/
                    $ttl_bill = $ttl_bill + $bill_comp_ttl;
                    $ttl_payment = $ttl_payment + $bill_payment_ttl;
                }
                $credit = 0.00;
                $debit = 0.00;
                $diff = $ttl_bill - $ttl_payment;
                if($diff < 0){/** Credit **/
                    $credit = $diff * -1;
                }
                else{/** Dedit **/
                    $debit = $diff;
                }
                $formatPrp[$key]['Bill']['year'] = $value1['Bill']['year'];
                $formatPrp[$key]['Bill']['ttl_bill'] = number_format($ttl_bill,2);
                $formatPrp[$key]['Bill']['ttl_payment'] = number_format($ttl_payment,2);
                $formatPrp[$key]['Bill']['credit'] = number_format($credit,2);
                $formatPrp[$key]['Bill']['debit'] = number_format($debit,2);
                
            }
            else{
                $formatPrp[$key]['Bill'] = array();
            }
        }
        
        return $formatPrp;
   }
   
   
   function __revenueReport($id, $revenue_year, $group_id){
        
        $prpData = $this->PropertyItem->find('all',array(
                'fields'=>array('id'),
                'conditions' => array('PropertyItem.location_id'=>$id, 'PropertyItem.property_group_id'=>$group_id, 'PropertyItem.deleted'=>0),
                'recursive'=>-1
            )
        );
        
        $prp_ids_arr = array();
        $frmt_properties = array();
        foreach($prpData as $key => $value){
            $prp_id = $value['PropertyItem']['id'];
            $prp_ids_arr[] = $prp_id;
            $frmt_properties[$prp_id] = $value;
            $frmt_properties[$prp_id]['BillProperty'] = array();
        }
        
        $billPropData = $this->BillProperty->find('all',array(
                'fields'=>array('id','bill_id','property_item_id'),
                'conditions' => array('BillProperty.property_item_id'=>$prp_ids_arr),
                'contain'=>array(
                    'Bill'=>array(
                        'fields'=>array('id','year'),
                    ),
                    'BillPayment'=>array('fields'=>array('id','amount')),
                ),
                'recursive'=>1
            )
        );
        
        foreach($billPropData as $key => $value){
          $prop_item_id =  $value['BillProperty']['property_item_id'];
          $frmt_properties[$prop_item_id]['BillProperty'][$key] = $value;
        }
               
        
        $formatPrp = array();
        foreach($frmt_properties as $key => $value){
            /** Process Bill **/
            $formatPrp[$key]['Property']['id'] = $value['PropertyItem']['id'];
            if($value['BillProperty']){
                $ttl_bill = 0.00;
                $ttl_payment = 0.00;
                foreach($value['BillProperty'] as $key1 => $value1){
                    if($revenue_year == 'All'){
                        $bill_comp_ttl = $this->__sumBillTotals($value1['Bill']['id']);
                        if($value1['BillPayment']){
                            $bill_payment_ttl =  $this->__sumPaymentTotals($value1['BillPayment']);
                        }
                        else{
                            $bill_payment_ttl = 0.00;
                        }
                        /** Totaling **/
                        $ttl_bill = $ttl_bill + $bill_comp_ttl;
                        $ttl_payment = $ttl_payment + $bill_payment_ttl;
                    }
                    else{
                        if($revenue_year == $value1['Bill']['year']){
                            $bill_comp_ttl = $this->__sumBillTotals($value1['Bill']['id']);
                            if($value1['BillPayment']){
                                $bill_payment_ttl =  $this->__sumPaymentTotals($value1['BillPayment']);
                            }
                            else{
                                $bill_payment_ttl = 0.00;
                            }
                            /** Totaling **/
                            $ttl_bill = $ttl_bill + $bill_comp_ttl;
                            $ttl_payment = $ttl_payment + $bill_payment_ttl;
                        }
                    }
                    
                }
                $credit = 0.00;
                $debit = 0.00;
                $diff = $ttl_bill - $ttl_payment;
                if($diff < 0){/** Credit **/
                    $credit = $diff * -1;
                }
                else{/** Dedit **/
                    $debit = $diff;
                }
                
                $formatPrp[$key]['Bill']['ttl_bill'] = number_format($ttl_bill,2);
                $formatPrp[$key]['Bill']['ttl_payment'] = number_format($ttl_payment,2);
                $formatPrp[$key]['Bill']['credit'] = number_format($credit,2);
                $formatPrp[$key]['Bill']['debit'] = number_format($debit,2);
                
            }
            else{
                $formatPrp[$key]['Bill'] = array();
            }
        }

        /** Now total for all properties **/
        $bill_totals = 0.00;
        $bill_payment_totals = 0.00;
        
        foreach($formatPrp as $key => $value){
            if($value['Bill']){
                $bill_totals = $bill_totals + $value['Bill']['ttl_bill'];
                $bill_payment_totals = $bill_payment_totals + $value['Bill']['ttl_payment'];
            }
        }
        $credit = 0.00;
        $debit = 0.00;
        $diff = $bill_totals - $bill_payment_totals;
        if($diff < 0){/** Credit **/
            $credit = $diff * -1;
        }
        else{/** Dedit **/
            $debit = $diff;
        }
        
        $overall = array();
        $overall['bill_totals'] = number_format($bill_totals,2);
        $overall['bill_payment_totals'] = number_format($bill_payment_totals,2);
        $overall['bill_credit_totals'] = number_format($credit,2);
        $overall['bill_debit_totals'] = number_format($debit,2);
        
        return $overall;
   }
   
      
}
?>
